package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import orm.ChildrenBehaviorPurpose;
import util.DBHelper;

public class ChildrenBehaviorPurposeDAO {
	
	
	public void insertChildrenBehaviorPurpose(Statement statement, 
			ArrayList<ChildrenBehaviorPurpose> cbpArr, String tpid) throws SQLException {
		ChildrenBehaviorPurpose cbp = null;
		for (int i = 0; i < cbpArr.size(); i++) {
			cbp = cbpArr.get(i);
			statement.execute("insert into childrenbehaviorpurpose values('" + tpid + "', " + 
					cbp.getItemID() + ", '" + cbp.getContent() + "', " + cbp.getResult() + ")");
		}
	}
	
	public void updateChildrenBehaviorPurpose(Statement statement,
			ArrayList<ChildrenBehaviorPurpose> cbpArr, String tpid) throws SQLException {
		ChildrenBehaviorPurpose cbp = null;
		for (int i = 0; i < cbpArr.size(); i++) {
			cbp = cbpArr.get(i);
			
			
			
			StringBuffer sqlStr = new StringBuffer();
			sqlStr.append("update childrenbehaviorpurpose set Content = '" + cbp.getContent() + "', Result = ");
			sqlStr.append(cbp.getResult() + " where TPID = '" + tpid + "' ");
			sqlStr.append("and ItemID = '"+cbp.getItemID()+"'");
			
			
			
			statement.execute(sqlStr.toString());
		}
	}
	
	public void deleteChildrenBehaviorPurpose(Statement statement, String tpid) throws SQLException {
		statement.execute("delete from childrenbehaviorpurpose where tpid='" + tpid + "'");
	}
	
	public ArrayList<ChildrenBehaviorPurpose> getChildrenBehaviorPurposeArrayByTPID(String tpid) throws SQLException {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<ChildrenBehaviorPurpose> al = new ArrayList<ChildrenBehaviorPurpose>();
		ChildrenBehaviorPurpose cbp = null;
		conn = DBHelper.getConnection();
		stmt = conn.createStatement();
		rs = stmt.executeQuery("select * from childrenbehaviorpurpose where TPID ='" + tpid + "'");
		
		while (rs.next()) {
			cbp = new ChildrenBehaviorPurpose();
			int i = 1;
			cbp.setTpID(rs.getString(i++));
			cbp.setItemID(rs.getInt(i++));
			cbp.setContent(rs.getString(i++));
			cbp.setResult(rs.getInt(i++));
			al.add(cbp);
		}
		
		rs.close();
		stmt.close();
		conn.close();
		
		return al;
	}
	
	
}
